Skip Headers

Oracle® Database Upgrade Guide 10g Release 2 (10.2) Part Number B14238-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 4 After Upgrading a Database

This chapter guides you through the procedures to perform after you have completed an upgrade of your database. The following topics are discussed:

- Tasks to Complete After All Upgrades

- Tasks to Complete Only After Manual Upgrades

- Tasks to Complete Only After Upgrading a Release 8.1.7 Database

## Tasks to Complete After All Upgrades

Complete the following tasks after you have upgraded your database, regardless of whether you performed the upgrade manually or by using the Database Upgrade Assistant (DBUA):

- Back Up the Database

- Update Environment Variables After the Upgrade (UNIX Systems Only)

- Set Threshold Values for Tablespace Alerts

- Migrate Tables from the LONG Datatype to the LOB Datatype

- Upgrade the TIMESTAMP Data

- Use the Latest Time Zone File for Clients

- Upgrade the Recovery Catalog

- Upgrade Statistics Tables Created by the DBMS_STATS Package

- Upgrade Externally Authenticated SSL Users

- Install Supplied Knowledge Bases

- Upgrade Change Data Capture

- Configure Secure HTTP

- Provide Anonymous Access to XML DB Repository Data via HTTP

- Update Your HTML DB Configuration

- Add New Features as Appropriate

- Develop New Administrative Procedures as Needed

### Back Up the Database

Make sure you perform a full backup of the production database.

See Also: Oracle Database Backup and Recovery Basics for details about backing up a database

### Update Environment Variables After the Upgrade (UNIX Systems Only)

If your operating system is UNIX, then make sure that the following environment variables point to the new release 10.2 directories:

- `ORACLE_HOME`

- `PATH`

- `ORA_NLS10`

  (Note that the `ORA_NLS10` environment variable replaces the `ORA_NLS33` environment variable, so you may need to unset `ORA_NLS33` and set `ORA_NLS10`.)

- `LD_LIBRARY_PATH`

Note: If you are upgrading a cluster database, then perform this step on all nodes in which this cluster database has instances configured.

See Also: Your operating system-specific Oracle Database installation documents for information about setting other important environment variables on your operating system.

### Set Threshold Values for Tablespace Alerts

An upgraded Oracle Database 10g database has the Tablespace Alerts disabled (the thresholds are set to null). Tablespaces in the database that are candidates for monitoring need to be identified and the appropriate threshold values set.

The default threshold values (for a newly created Oracle Database 10g database) are:

- 85% full warning

- 97% full critical

### Migrate Tables from the LONG Datatype to the LOB Datatype

`LOB` datatypes (`BFILE`, `BLOB`, `CLOB`, and `NCLOB`) can provide many advantages over `LONG` datatypes. See Oracle Database Concepts for information about the differences between `LONG` and `LOB` datatypes.

In Oracle9i release 9.0.1 and later, the `ALTER TABLE` statement can be used to change the datatype of a `LONG` column to `CLOB` and that of a `LONG RAW` column to `BLOB`.

In the following example, the `LONG` column named `long_col` in table `long_tab` is changed to datatype `CLOB`:

    SQL> ALTER TABLE Long_tab MODIFY ( long_col CLOB );

After using this method to change `LONG` columns to LOBs, all the existing constraints and triggers on the table will still be usable. However, all the indexes, including Domain indexes and Functional indexes, on all columns of the table will become unusable and will have to be rebuilt using an `ALTER INDEX ... REBUILD` statement. Also, the Domain indexes on the `LONG` column will have to be dropped before changing the `LONG` column to a LOB.

See Also: Oracle Database Application Developer's Guide - Large Objects for information about modifying applications to use LOB data

### Upgrade the TIMESTAMP Data

If your database has `TIMESTAMP WITH TIMEZONE` data, you must update the data so that it is converted and stored based on the new time zone rules that come with the upgrade. (See "TIMESTAMP WITH TIMEZONE Datatype").

If you used the export utility to export a copy of the affected tables, you should now use the import utility to import your data from these tables back into your database. The import utility will update the timestamp data as it imports.

If you used the manual script method, you will need to update the affected timestamp data based on your backed up table. For example, if you previously backed up your table, you need to run an update statement similar to the one below to update your timestamp data.

    UPDATE tztab t SET t.y =
      (SELECT to_timestamp_tz(t1.y,'YYYY-MM-DD HH24.MI.SSXFF TZR')
    FROM tztab_back t1
    WHERE t.x=t1.x);

Although the transition rule changes for some time zone regions may affect data of `TIMESTAMP WITH LOCAL TIME ZONE` datatype, there is no way to upgrade the data. The data cannot be upgraded because this type does not preserve the original time zone/region associated with the data.

Time zone regions in Brazil and Israel may have frequent transition rules changes, perhaps as often as every year. Use the time zone offset instead of the time zone region name to avoid storing inconsistent data.

### Use the Latest Time Zone File for Clients

Customers using time zone regions that have been updated in version 2 of the time zone files are required to update all Oracle9i Database clients and databases that will communicate with an Oracle Database 10g server. This ensures that all environments will have the same version of the time zone file. Upgrading to the latest time zone file is not a requirement for customers that do not use the `TIMESTAMP WITH TIME ZONE` type or manipulate data from regions with frequent changes to their time zone transition rules. However Oracle recommends this action to avoid future issues. Users who need to update their time zone files to version 2 can find the following information on Oracle MetaLink (`http://metalink.oracle.com`):

- `readme.txt` contains the list of time zone regions that have changed from version 1 to version 2

- Actual time zone files for version 2 for the Oracle9i Database release

Oracle Database 10g clients that communicate with Oracle Database 10g servers automatically get version 2 of the time zone file, so there is no need to download the new time zone file.

See Also: `$ORACLE_HOME/oracore/zoneinfo/readme.txt` for detailed information about time zone file updates

### Upgrade the Recovery Catalog

For information about upgrading the recovery catalog, see Oracle Database Backup and Recovery Advanced User's Guide.

### Upgrade Statistics Tables Created by the DBMS_STATS Package

If you created statistics tables using the `DBMS_STATS.CREATE_STAT_TABLE` procedure, then upgrade these tables by executing the following procedure:

    EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('scott', 'stat_table');

where `SCOTT` is the owner of the statistics table and `STAT_TABLE` is the name of the statistics table. Execute this procedure for each statistics table.

### Upgrade Externally Authenticated SSL Users

If you are using externally authenticated SSL users, you must run the following command to upgrade those users:

    $ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring
    <hostname:port_no:sid> --dbuser <db admin> --dbuserpassword
    <password> -a

See Also: Oracle Database Enterprise User Administrator's Guide for more information on the `extusrupgrade` script

### Install Supplied Knowledge Bases

The Supplied Knowledge Bases have been moved to be part of the companion Oracle Database 10g Products and are not immediately available after an upgrade to Oracle Database 10g. Any Text features dependent on the Supplied Knowledge Bases which were available before the upgrade will not function after the upgrade. To re-enable such features, you must install the Supplied Knowledge Bases from the installation media.

After an upgrade, all user-extensions to the Supplied Knowledge Bases must be regenerated. These changes affect all databases installed in the given `ORACLE_HOME`.

See Also: Oracle Text Application Developer's Guide for information about Supplied Knowledge Bases The post-installation tasks section of the Oracle Database Installation Guide for companion products for your platform

### Upgrade Change Data Capture

Beginning with Oracle 10g Release 10.2, Asynchronous Change Data Capture (CDC) no longer requires the same operating system for source and target databases. This feature enables a heterogeneous CDC setup with different operating systems and Oracle versions, enabling asynchronous CDC to leverage any existing Oracle9i Release 2 (9.2) system as a source.

See the Oracle Database Data Warehousing Guide for complete information about how to upgrade a release 9.2 or 10.1 Oracle Database to release 10.2 with Change Data Capture. The discussion describes the supported configurations for the Distributed HotLog mode of Change Data Capture as well as the restrictions.

### Configure Secure HTTP

If HTTPS access to Oracle XML DB is required, then you must provide correct configuration information, as described in this section.

When a database is upgraded to release 10.2, the XML schema for the XDB configuration file is automatically upgraded so that the XDB configuration file (located at `/xdbconfig.xml` in the repository) can have two additional elements, `http2-port` and `http2-protocol`. These elements are not added to the XDB configuration file by default during an upgrade. If you wish to have support for HTTPS, you must edit the configuration file to add these two new elements (see the XML schema for their exact locations), and to set the value of `http2-protocol` to `tcps`. The value of `http2-port` should be different from the value of `http-port`.

In addition to specifying the parameters `http2-port` and `http2-protocol` in the XDB configuration file, you must configure the database and the listener to enable Oracle XML DB to use HTTPS. Additionally, if the following steps were not taken before the upgrade, then you must perform them after the upgrade:

1. Enable the HTTP listener and the database to use SSL

2. Enable launching of a TCPS dispatcher

For more information on how to do this, see Oracle XML DB Developer's Guide.

### Provide Anonymous Access to XML DB Repository Data via HTTP

If anonymous access to XML DB repository data via HTTP is not required, then you do not have to perform this step. If anonymous access to XML DB repository data via HTTP is required, then you must provide correct configuration information, as described in this section. The administrator must carefully consider whether anonymous access is to be allowed, given the inherent security risks.

When a database is upgraded to Oracle 10g Release 2, the XML schema for the XML DB configuration file (located at `/xdbconfig.xml` in the repository) is automatically upgraded so that it can have an additional element, `allow-repository-anonymous-access`. This element is of Boolean type which means it can have a value of `true` or `false`. It can be used to disallow unauthenticated access to your Oracle XML DB Repository data through HTTP even if you unlock the `ANONYMOUS` user account. It is not added to the XML DB configuration file by default during an upgrade but when this element is missing, it is interpreted as `false`.

Therefore, anonymous access to XML DB repository data via HTTP is disabled when you upgrade to Oracle 10g Release 2. If you wish to have anonymous access to XML DB repository data via HTTP, you must change the configuration file to set this new element to `true`, in addition to unlocking the `ANONYMOUS` user account.

Caution: There is an inherent security risk associated with allowing unauthenticated access to the repository.

See Also: Oracle XML DB Developer's Guide for more information about the `allow-repository-anonymous-access` element and configuring Oracle XML DB

### Update Your HTML DB Configuration

To update your HTML DB configuration, you must complete a series of post-installation steps. These steps are described in the section on post-installation tasks in the Oracle Database Installation Guide for companion products for your particular platform. In particular, refer to the sections on Post-Installation Tasks for Oracle HTML DB and Post-Installation Tasks for Oracle HTTP Server.

### Add New Features as Appropriate

Oracle Database New Features describes many of the new features available in the new Oracle Database release. Determine which of these new features can benefit the database and applications; then, develop a plan for using these features.

It is not necessary to make any immediate changes to begin using your new Oracle Database. You may prefer to introduce these enhancements into your database and corresponding applications gradually.

Chapter 6, "Upgrading Your Applications" describes ways to enhance your applications so that you can take advantage of new Oracle Database features. However, before you implement new Oracle Database features, test your applications and successfully run them with the upgraded database.

### Develop New Administrative Procedures as Needed

After familiarizing yourself with new Oracle Database features, review your database administration scripts and procedures to determine whether any changes are necessary.

Coordinate your changes to the database with the changes that are necessary for each application. For example, by enabling integrity constraints in the database, you may be able to remove some data checking from your applications.

## Tasks to Complete Only After Manual Upgrades

If you are performing a manual upgrade rather than using the Database Upgrade Assistant (DBUA), then you must perform the following tasks after your database is upgraded:

- Change Passwords for Oracle-Supplied Accounts

- Migrate Your Initialization Parameter File to a Server Parameter File

- Upgrade Oracle Text

- Upgrade the Oracle Cluster Registry (OCR) Configuration

- Adjust the Initialization Parameter File for the New Release

- Install and Configure Enterprise Manager Database Control

### Change Passwords for Oracle-Supplied Accounts

Depending on the release from which you upgraded, there may be new Oracle-supplied accounts. Oracle recommends that you lock all Oracle-supplied accounts except for `SYS` and `SYSTEM`, and expire their passwords, thus requiring new passwords to be specified when the accounts are unlocked.

You can view the status of all accounts by issuing the following SQL statement:

    SQL> SELECT username, account_status
             FROM dba_users
             ORDER BY username;

To lock and expire passwords, issue the following SQL statement:

    SQL> ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;

### Migrate Your Initialization Parameter File to a Server Parameter File

If you are currently using a traditional initialization parameter file, perform the following steps to migrate to a server parameter file:

1. If the initialization parameter file is located on a client machine, transfer the file from the client machine to the server machine.

   Note: If you are using Real Application Clusters, then you must combine all of your instance-specific initialization parameter files into a single initialization parameter file. Instructions for doing this, and other actions unique to using a server parameter file for cluster databases, are discussed in: Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide The Real Application Cluster installation guide for your operating system

2. Create a server parameter file using the `CREATE SPFILE` statement. This statement reads the initialization parameter file to create a server parameter file. The database does not have to be started to issue a `CREATE SPFILE` statement.

3. Start up the instance using the newly-created server parameter file.

See Also: Oracle Database Administrator's Guide for more information about creating server parameter files Oracle Database SQL Reference for information about the `CREATE SPFILE` statement

### Upgrade Oracle Text

After an upgrade to Oracle Database 10g, copy the following files from the previous `ORACLE_HOME` to the new `ORACLE_HOME`:

- Stemming user-dictionary files

- User-modified `KOREAN_MORPH_LEXER` dictionary files

- `USER_FILTER` executables

These files affect all databases installed in the given `ORACLE_HOME`.

See Also: Oracle Text Reference for more information about these files Oracle Text Application Developer's Guide for information about upgrading your applications from previous releases of Oracle Text

### Upgrade the Oracle Cluster Registry (OCR) Configuration

If you are using Oracle Cluster Services, then you must upgrade the Oracle Cluster Registry (OCR) keys for the database.

Use one of the following options to upgrade the OCR configuration to 10g:

- Use `srvconfig` from the 10g ORACLE_HOME. For example:

      % srvconfig -upgrade -dbname db_name -orahome pre-10g_Oracle_home

- Run `srvctl. For example:`

      pre-10g_Oracle_home/bin/srvctl remove database -d db_name
      10g_Oracle_home/bin/srvctl add database -d db_name -o 10g_Oracle_home
      10g_Oracle_home/bin/srvctl add instance -d db_name -i instance -n node

### Adjust the Initialization Parameter File for the New Release

Each release of the Oracle Database introduces new initialization parameters, deprecates some initialization parameters, and makes some initialization parameters obsolete. You should adjust the parameter file to account for these changes and to take advantage of new initialization parameters that may be beneficial to your system.

See Also: Oracle Database Reference for a list of the new initialization parameters in release 10.2, and for information about each parameter Appendix A, "Initialization Parameter and Data Dictionary Changes" for lists of obsolete and deprecated initialization parameters in release 10.2

The `COMPATIBLE` initialization parameter controls the compatibility level of your database. When you are certain that you no longer need the ability to downgrade your database back to its original version, set the `COMPATIBLE` initialization parameter based on the compatibility level you want for your new database.

See Also: "Setting the COMPATIBLE Initialization Parameter" for information

### Install and Configure Enterprise Manager Database Control

If you want to use Enterprise Manager Database Control with your database, then you must install and configure it. For information on how to do this, see the section on Configuring the Database Control with EMCA in Oracle Enterprise Manager Advanced Configuration.

## Tasks to Complete Only After Upgrading a Release 8.1.7 Database

Complete the following additional tasks only if you upgraded your database from release 8.1.7.

- Upgrade User NCHAR Columns

- Migrate Your Server Manager Line Mode Scripts to SQL*Plus

### Upgrade User NCHAR Columns

If you upgraded from a version 8 release and your database contains user tables with `NCHAR` columns, you must upgrade the `NCHAR` columns before they can be used in the Oracle Database.

The following steps convert your `NCHAR` columns from the old format and character set to the new Oracle Database format. In addition, if your old National Character Set was UTF8, it will remain UTF8 in the Oracle Database. However, your National Character Set will be converted to AL16UTF16 if it was not UTF8 in the old release.

You can override the default upgrade selection of the National Character Set. That is, a version 8 UTF8 National Character Set can be converted to an Oracle Database AL16UTF16 National Character Set or a version 8 non-UTF8 National Character Set can be converted to an Oracle Database UTF8 National Character Set.

You will encounter the following error when attempting to use the `NCHAR` columns in the Oracle Database until you perform the steps in this section:

    ORA-12714: invalid national character set specified

To upgrade user tables with `NCHAR` columns, perform the following steps:

1. Log in to the system as the owner of the Oracle home directory.

2. At a system prompt, change to the `ORACLE_HOME``/rdbms/admin` directory.

3. Start SQL*Plus.

4. Connect to the database instance as a user with `SYSDBA` privileges.

5. If the instance is running, shut it down using `SHUTDOWN IMMEDIATE`:

       SQL> SHUTDOWN IMMEDIATE

6. Start up the instance in `RESTRICT` mode:

       SQL> STARTUP RESTRICT

   You may need to use the `PFILE` option to specify the location of your initialization parameter file.

7. Run `utlnchar.sql`:

       SQL> @utlnchar.sql

   Alternatively, to override the default upgrade selection, run `n_switch.sql`:

       SQL> @n_switch.sql

8. Shut down the instance:

       SQL> SHUTDOWN IMMEDIATE

9. Exit SQL*Plus.

### Migrate Your Server Manager Line Mode Scripts to SQL*Plus

The Oracle Database no longer supports the use of Server Manager. If you run SQL scripts using Server Manager line mode, you must modify these scripts so that they are compatible with SQL*Plus. Appendix B, "Migrating from Server Manager to SQL*Plus" contains instructions for modifying your Server Manager line mode scripts to work with SQL*Plus.